"""
Case Type   : 客户场景-邮储
Case Name   : 简单update语句支持bypass优化
Create At   : 2021/11/25
Owner       : wan005
Description :
    1.创建表并插入数据
    2.解析插入语句
    3.创建函数
    4.创建索引
    5.解析更新语句
    6.解析更新语句where为复合条件
    7.解析更新语句条件使用函数或非btree索引
Expect      :
    1.创建表并插入数据成功
    2.插入语句被优化【Bypass】
    3.创建函数成功
    4.创建索引成功
    5.更新语句被优化【Bypass】
    6.更新语句被优化
    7.更新语句不会被优化【No Bypass】
History     :
"""
import unittest
import os

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Finance(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.constant = Constant()
        self.commonsh = CommonSH("PrimaryDbUser")
        status = self.commonsh.get_db_cluster_status("detail")
        self.log.info(status)
        self.assertTrue("Normal" in status or "Degraded" in status)
        status = self.commonsh.restart_db_cluster()
        self.log.info(status)
        status = self.commonsh.get_db_cluster_status("detail")
        self.log.info(status)
        self.assertTrue("Normal" in status or "Degraded" in status)

        result = self.commonsh.execut_db_sql("show enable_opfusion")
        self.assertNotIn("ERROR", result)
        self.enable_opfusion = result.splitlines()[-2].strip()
        self.log.info(self.enable_opfusion)
        result = self.commonsh.execut_db_sql("show enable_bitmapscan")
        self.assertNotIn("ERROR", result)
        self.enable_bitmapscan = result.splitlines()[-2].strip()
        self.log.info(self.enable_bitmapscan)
        result = self.commonsh.execut_db_sql("show enable_seqscan")
        self.assertNotIn("ERROR", result)
        self.enable_seqscan = result.splitlines()[-2].strip()
        self.log.info(self.enable_seqscan)
        result = self.commonsh.execut_db_sql("show opfusion_debug_mode")
        self.assertNotIn("ERROR", result)
        self.opfusion_debug_mode = result.splitlines()[-2].strip()
        self.log.info(self.opfusion_debug_mode)

        self.t_name = "t_finance_008"
        self.f_name = "f_finance_008"

    def test_finance(self):
        text = f"-----step:修改参数; expect:成功-----"
        self.log.info(text)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          "enable_opfusion=on")
        self.assertTrue(res, "执行失败:" + text)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          "enable_bitmapscan=off")
        self.assertTrue(res, "执行失败:" + text)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          "enable_seqscan=off")
        self.assertTrue(res, "执行失败:" + text)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          "opfusion_debug_mode='log'")
        self.assertTrue(res, "执行失败:" + text)

        text = f"-----step1:创建表; expect:成功-----"
        self.log.info(text)
        sql = f"create table {self.t_name}(c_int int,c_money money " \
            f"default -12345, c_text text, c_char char(20), " \
            f"c_point point);" \
            f"insert into {self.t_name} values(generate_series(1,20000), " \
            f"random()*1000::money, random()::text, random()::char(20), " \
            f"point(1,2));"
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result,
                      "执行失败:" + text)

        text = "-----step2:解析插入语句，仅1个vaules; expect:解析结果包含：【Bypass】-----"
        self.log.info(text)
        sql = f'''explain insert into {self.t_name} 
        values(9999,default,'***~~~::: hello', '8., world', point(2,3));'''
        self.log.info(sql)
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn("Bypass", result, "执行失败:" + text)

        text = "-----step3:创建函数;expect:成功-----"
        self.log.info(text)
        sql = f'''create function {self.f_name}(integer, integer) returns integer
        as 'select \$1 + \$2;'
        language sql
        immutable
        returns null on null input;'''
        self.log.info(sql)
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, result,
                      "执行失败:" + text)

        text = "-----step4:创建索引; expect:成功-----"
        self.log.info(text)
        sql = f'''create index index_008_01 on {self.t_name}(c_text);
                create index index_008_02 on {self.t_name}(c_int);
                create index index_008_03 on {self.t_name} 
                using gist(c_point);
                create index index_008_04 on {self.t_name}(c_money);
                create index index_008_05 on {self.t_name}(c_int, c_text);'''
        self.log.info(sql)
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败:" + text)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG,
                      result, "执行失败:" + text)

        text = "-----step5:解析更新语句; expect:解析结果包含【Bypass】-----"
        self.log.info(text)
        sql = f'''explain update {self.t_name} set c_int = {self.f_name}(2,2) 
                where c_text='***~~~::: hello';
                explain update {self.t_name} set c_money = (-2.3)::money 
                where c_text=\'***~~~::: hello\''''
        sql_list = sql.split(';')
        for sql in sql_list:
            self.log.info(sql)
            result = self.commonsh.execut_db_sql(sql)
            self.log.info(result)
            self.assertIn("Bypass", result, "执行失败:" + text)

        text = "-----step6:解析更新语句+where; expect:更新语句被优化-----"
        self.log.info(text)
        sql = f"explain update {self.t_name} set c_int = 1+2 " \
            f"where c_text='***~~~::: hello' and c_int<3;"
        self.log.info(sql)
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn("Bypass", result, "执行失败:" + text)

        text = "-----step7:解析更新语句条件使用函数或非btree索引; expect:解析结果包含【No Bypass】-----"
        self.log.info(text)
        sql = f'''explain update {self.t_name} set c_int = 1+2 
                where c_point <^ point(5,5);
                explain update {self.t_name} set c_money = (-2.3)::money 
                where c_money<(-1)::money'''
        sql_list = sql.split(';')
        for sql in sql_list:
            self.log.info(sql)
            result = self.commonsh.execut_db_sql(sql)
            self.log.info(result)
            self.assertIn("No Bypass", result, "执行失败:" + text)

    def tearDown(self):
        text = "-----step8:清理环境; expect:成功-----"
        self.log.info(text)
        result_list = []
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f"enable_opfusion="
                                          f"{self.enable_opfusion}")
        result_list.append(res)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f"enable_bitmapscan="
                                          f"{self.enable_bitmapscan}")
        result_list.append(res)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f"enable_seqscan="
                                          f"{self.enable_seqscan}")
        result_list.append(res)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f"opfusion_debug_mode="
                                          f"{self.opfusion_debug_mode}")
        result_list.append(res)

        sql = f"drop table {self.t_name} cascade;" \
            f"drop function {self.f_name}(integer, integer)"
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        status = self.commonsh.get_db_cluster_status("detail")
        self.log.info(status)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result,
                      "执行失败:" + text)
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG,
                      result, "执行失败:" + text)
        for res in result_list:
            self.assertTrue(res, "执行失败:" + text)
        self.assertTrue("Normal" in status or "Degraded" in status)
        self.log.info(f"-----{os.path.basename(__file__)} finish=")
